import pandas as pd
from app import app, db
from models import User, Tag, UserTag
from datetime import datetime
import re
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def validate_email(email):
    """验证邮箱格式"""
    pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    return bool(re.match(pattern, email))

def validate_phone(phone):
    """验证手机号格式"""
    pattern = r'^1[3-9]\d{9}$'
    return bool(re.match(pattern, phone))

def clean_string(s):
    """清理字符串中的特殊字符"""
    if isinstance(s, str):
        return s.strip().replace('\n', '').replace('\r', '')
    return s

def import_users_from_csv(file_path):
    """从CSV文件批量导入用户"""
    try:
        # 读取CSV文件
        df = pd.read_csv(file_path)
        required_columns = ['username', 'email', 'phone']
        
        # 检查必填字段
        for col in required_columns:
            if col not in df.columns:
                raise ValueError(f"缺少必填字段: {col}")
        
        # 获取新用户标签
        with app.app_context():
            new_user_tag = Tag.query.filter_by(name='新注册用户').first()
            if not new_user_tag:
                logger.error("未找到'新注册用户'标签")
                return
        
        success_count = 0
        error_count = 0
        error_records = []
        
        # 处理每一行数据
        for index, row in df.iterrows():
            try:
                # 数据清洗
                username = clean_string(row['username'])
                email = clean_string(row['email'])
                phone = clean_string(str(row['phone']))
                
                # 数据验证
                if not validate_email(email):
                    raise ValueError(f"邮箱格式不正确: {email}")
                if not validate_phone(phone):
                    raise ValueError(f"手机号格式不正确: {phone}")
                
                # 设置默认值
                membership_level = clean_string(row.get('membership_level', '普通会员'))
                try:
                    consumption_score = float(row.get('consumption_score', 0))
                except (ValueError, TypeError):
                    consumption_score = 0
                
                try:
                    status = bool(int(row.get('status', 1)))
                except (ValueError, TypeError):
                    status = True
                
                # 创建用户对象
                user = User(
                    username=username,
                    email=email,
                    phone=phone,
                    membership_level=membership_level,
                    registration_date=datetime.now(),
                    consumption_score=consumption_score,
                    status=status
                )
                
                with app.app_context():
                    # 检查邮箱是否已存在
                    existing_user = User.query.filter_by(email=email).first()
                    if existing_user:
                        raise ValueError(f"邮箱已存在: {email}")
                    
                    # 保存用户
                    db.session.add(user)
                    db.session.flush()  # 获取用户ID
                    
                    # 添加新用户标签
                    user_tag = UserTag(
                        user_id=user.id,
                        tag_id=new_user_tag.id,
                        last_updated=datetime.now()
                    )
                    db.session.add(user_tag)
                    db.session.commit()
                    
                    success_count += 1
                    logger.info(f"成功导入用户: {username}")
                    
            except Exception as e:
                error_count += 1
                error_records.append({
                    'row': index + 2,  # Excel行号从2开始
                    'error': str(e)
                })
                logger.error(f"第{index + 2}行导入失败: {str(e)}")
                continue
        
        # 返回导入结果
        result = {
            'total': len(df),
            'success': success_count,
            'error': error_count,
            'error_records': error_records
        }
        
        return result
    
    except Exception as e:
        logger.error(f"导入过程发生错误: {str(e)}")
        raise

if __name__ == '__main__':
    # 测试导入
    try:
        result = import_users_from_csv('user_import_template.csv')
        print("导入结果:", result)
    except Exception as e:
        print("导入失败:", str(e)) 